"""
这个类封装了DataFrame对SQL SERVER数据库的操作
"""
from util import ip
from sqlalchemy import create_engine, text
import pandas as pd

class SQLServer_df:
    _instance = None

    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            cls._instance = super(SQLServer_df, cls).__new__(cls, *args, **kwargs)
        return cls._instance

    def __init__(self):
        self.server = ip
        self.database = 'system_db'
        self.username = 'sa'
        self.password = 'cj126414.'
        self.engine = create_engine(
            f'mssql+pyodbc://{self.username}:{self.password}@{self.server}/{self.database}?driver=ODBC+Driver+17+for+SQL+Server',
            pool_size=20, max_overflow=0)

    # 查询SQL SERVER数据库
    def Query(self, sql, params=None):
        try:
            df = pd.read_sql_query(sql, self.engine, params=params)
            return df
        except Exception as e:
            print(f"An error occurred: {e}")
            # 返回空的DataFrame
            return pd.DataFrame()

    # 把DataFrame对象写入SQL Server数据库
    def write_to_sql(self, df, table_name, if_exists='append', chunksize=100000):
        """
        将DataFrame对象写入SQL Server数据库。
        参数：
        df: 要写入的DataFrame对象。
        table_name: 数据库中的表名。
        if_exists: append向表中追加数据，replace替换表中的数据，fail在表中没有数据时创建新表，ignore忽略错误。
        dtype: 字段类型，比如：dtype = {'Item': String(20), 'C_version': String(100), 'H_version': String(200)}
        chunksize: 每次写入的行数，默认为1000000。
        """
        try:
            # 将 DataFrame 写入数据库
            df.to_sql(table_name, self.engine, if_exists=if_exists, index=False, chunksize=chunksize)
        except Exception as e:
            print(f"An error occurred: {e}")
            return 0
        return len(df)

    # 删除SQL Server数据库中的记录
    def delete_from_sql(self, df, table_name, column):
        """
        根据DataFrame对象的数据删除SQL Server数据库中的记录。

        参数：
        df: DataFrame对象，包含要删除的记录的标识。
        table_name: 数据库中的表名。
        column: 要匹配的列名。
        """
        # 遍历DataFrame对象，删除每一行对应的记录
        try:
            with self.engine.connect() as connection:
                for index, row in df.iterrows():
                    # 构造SQL语句
                    sql_delete = text(f"DELETE FROM {table_name} WHERE {column} = :value")

                    # 执行SQL语句
                    connection.execute(sql_delete, parameters={'value': row[column]})
                    # 提交
                    connection.commit()
                    return len(df)
        except Exception as e:
            print(f"An error occurred: {e}")
            return 0


    # 批量删除,先把数据插入到临时表中,再批量删除，效率比delete_from_sql高
    def bulk_delete(self, df, table_name, temp_table_name, column):
        """
        使用BULK DELETE方法删除SQL Server数据库中的记录。

        参数：
        df: DataFrame对象，包含要删除的记录的标识。
        table_name: 数据库中的表名。
        temp_table_name: 临时表的表名。
        column: 要匹配的列名。
        """

        try:
            # 将DataFrame对象的数据插入到临时表中
            df.to_sql(temp_table_name, self.engine, if_exists='replace', index=False)

            # 构造SQL语句
            sql_delete = text(
                f"DELETE FROM {table_name} WHERE {column} IN (SELECT {column} FROM {temp_table_name})")

            # 执行SQL语句
            with self.engine.connect() as connection:
                connection.execute(sql_delete)
                # 提交
                connection.commit()
                return len(df)
        except Exception as e:
            print(f"An error occurred: {e}")
            return 0

